package com.fcscs.ecms.dao.hibernate;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.fcscs.core.dao.ConnectionManager;
import com.fcscs.core.domain.BaseEntity.DBLoc;
import com.fcscs.cservice.domain.CookOptionItem;
import com.fcscs.cservice.domain.SpicyDescription;
import com.fcscs.cservice.domain.model.CookOptionItemView;
import com.fcscs.cservice.domain.model.SpicyView;
import com.fcscs.cservice.ws.WebServiceUtils;
import com.fcscs.ecms.dao.ECMSInRoomConfDao;

@SuppressWarnings("unchecked")
@Repository("ecmsInRoomConfDao")
public class ECMSInRoomConfDaoHibernate implements ECMSInRoomConfDao {

	
	private static DBLoc INROOMBLoc = new CookOptionItem().getDBLoc();
	
	@Autowired
	private ConnectionManager connMgr;
	
	private static final String FIND_COOKOPTION_LIST = "FROM CookOptionItem ci Where ci.orgId=:orgId and ci.propertyId=:propId ";
	
	private static final String FIND_SPICYDESCRIPTION_LIST = "FROM SpicyDescription sv Where sv.orgId=:orgId and sv.propertyId=:propId ";
	
	private static final String COOKOPTIONITEMTAKEN_CNT = "select count(*) from cook_option_item where org_id = :orgId and " +
		" property_id = :propId and name = :name and id <> :id and active = true ";
	
	private static final String SPICYDESCRIPTION_CNT = "select count(*) from spicy_description where org_id = :orgId and " +
		" property_id = :propId and name = :name and id <> :id and active = true ";

	private static final String FIND_COOKOPTIONITEM_VIEW ="select tt.id as optionItemId, tt.name as item, " +
			"tt.name_sec_lang as itemSecLang, tt.name_thd_lang as itemThdLang " +
			" from cook_option_item tt where tt.org_id=:orgId and tt.property_id=:propId";
	
	@Override
	public CookOptionItem getCookOptionItem(long id) {
		return (CookOptionItem) connMgr.getCurrentSession(INROOMBLoc).load(CookOptionItem.class, id);
	}

	@Override
	public List<CookOptionItem> getCookOptionItemList(long orgId, long propId,boolean isCurrent) {
		
		Query  q = connMgr.getCurrentSession(INROOMBLoc).createQuery(FIND_COOKOPTION_LIST);
		q.setLong("orgId", orgId);
		q.setLong("propId",propId);
		return q.list();
	}

	@Override
	public List<CookOptionItem> getCookOptionItemList(long orgId, long propId,long versionId) {
		Query  q = connMgr.getCurrentSession(INROOMBLoc).createQuery(FIND_COOKOPTION_LIST);
		q.setLong("orgId", orgId);
		q.setLong("propId",propId);
		return q.list();
	}

	@Override
	public List<CookOptionItem> saveCookOptionItemList(List<CookOptionItem> coiList) {
		
		Session session = connMgr.getCurrentSession(INROOMBLoc);
		for(CookOptionItem coi : coiList){
			session.saveOrUpdate(coi);
		}
		return coiList;
	}

	@Override
	public CookOptionItem createCookOptionItem(CookOptionItem coi) {
		connMgr.getCurrentSession(INROOMBLoc).save(coi);
		return coi;
	}

	@Override
	public CookOptionItem saveCookOptionItem(CookOptionItem coi) {
		connMgr.getCurrentSession(INROOMBLoc).saveOrUpdate(coi);
		return coi;
	}

	@Override
	public boolean deleteCookOptionItem(CookOptionItem coi) {
		String sql = "Update CookOptionItem ci set ci.active = false where ci.id=:id";
		Query q = connMgr.getCurrentSession(INROOMBLoc).createQuery(sql);
		q.setLong("id", coi.getId());
		
		return (q.executeUpdate()== 1);
	}

	@Override
	public boolean permDeleteCookOptionItem(CookOptionItem coi) {
		try {
			connMgr.getCurrentSession(INROOMBLoc).delete(coi);
			return true;
		} catch (Exception e) {
			return false;
		}

	}

	@Override
	public boolean isCookOptionItemTaken(long orgId, long propId, String name,Long id, long versionId) {
		
		Query q  = connMgr.getCurrentSession(INROOMBLoc).createSQLQuery(COOKOPTIONITEMTAKEN_CNT);
		q.setString("name", name);
		q.setLong("id", id == null? -1 : id);
		q.setLong("orgId", orgId);
		q.setParameter("propId", propId, Hibernate.LONG);
		
		return ((Number) q.uniqueResult()).intValue() > 0;
	}

	@Override
	public List<CookOptionItemView> getCookOptionItemViewList(long orgId,long propId) {
		
		SQLQuery sqlQu =  connMgr.getCurrentSession(INROOMBLoc).createSQLQuery(FIND_COOKOPTIONITEM_VIEW);
		sqlQu.setLong("orgId", orgId);
		sqlQu.setParameter("propId", propId, Hibernate.LONG);

		sqlQu.addScalar("optionItemId", Hibernate.LONG).
		addScalar("item", Hibernate.STRING).
		addScalar("itemSecLang", Hibernate.STRING).
		addScalar("itemThdLang", Hibernate.STRING);

		return sqlQu.setResultTransformer(Transformers.aliasToBean(CookOptionItemView.class)).list();
	}

	@Override
	public SpicyDescription getSpicyDescription(long id) {
		return (SpicyDescription) connMgr.getCurrentSession(INROOMBLoc).get(SpicyDescription.class, id);
	}

	@Override
	public List<SpicyDescription> getSpicyDescriptionList(long orgId,long propId, boolean isCurrent) {

		Query q = connMgr.getCurrentSession(INROOMBLoc).createQuery(FIND_SPICYDESCRIPTION_LIST);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		
		return q.list();
	}

	@Override
	public List<SpicyDescription> getSpicyDescriptionList(long orgId,long propId, long versionId) {
		
		Query q = connMgr.getCurrentSession(INROOMBLoc).createQuery(FIND_SPICYDESCRIPTION_LIST);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		
		return q.list();
	}

	@Override
	public SpicyDescription createSpicyDescription(SpicyDescription spicy) {
			connMgr.getCurrentSession(INROOMBLoc).save(spicy);
		return spicy;
	}

	@Override
	public SpicyDescription saveSpicyDescription(SpicyDescription spicy) {
			connMgr.getCurrentSession(INROOMBLoc).saveOrUpdate(spicy);
		return spicy;
	}

	@Override
	public boolean deleteSpicyDescription(SpicyDescription spicy) {
		String sql = "Update SpicyDescription sd set sd.active = false where sd.id=:id";
		Query q = connMgr.getCurrentSession(INROOMBLoc).createQuery(sql);
		q.setLong("id", spicy.getId());
		
		return (q.executeUpdate()== 1);
	}

	@Override
	public boolean permDeleteSpicyDescription(SpicyDescription spicy) {
		try {
			connMgr.getCurrentSession(INROOMBLoc).delete(spicy);
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	
	@Override
	public boolean isSpicyDescriptionTaken(long orgId, long propId,String name, Long id, long versionId) {

		Query q  = connMgr.getCurrentSession(INROOMBLoc).createSQLQuery(SPICYDESCRIPTION_CNT);
		q.setString("name", name);
		q.setLong("id", id == null? -1 : id);
		q.setLong("orgId", orgId);
		q.setParameter("propId", propId, Hibernate.LONG);
		
		return ((Number) q.uniqueResult()).intValue() > 0;
	}

	@Override
	public List<SpicyView> getSpicyViewList(long orgId, long propId) {
		
		//TODO unit test
		String sql = "select s.id, s.name, i.image from SpicyDescription s ,ImageResource i " +
					"where s.iconId=i.id and s.orgId=:orgId and s.propertyId=:propId";
		
		Query sqlQu = connMgr.getCurrentSession(INROOMBLoc).createQuery(sql);
		sqlQu.setParameter("orgId", orgId);
		sqlQu.setParameter("propId", orgId);
		
		List<SpicyView> list = new ArrayList<SpicyView>();
		List<Object[]> categories = (List<Object[]>)sqlQu.list();
		for(Object[] o : categories) {
			SpicyView spi = new SpicyView();
			spi.setSpicyId((Long) o[0]);
			spi.setName((String) o[1]);
			spi.setIconFile(WebServiceUtils.convertToString((byte[])o[2]));
			list.add(spi);
		 }
		
		return list;
	}

}
